package com.google.code.gaeapptest.junit;

import static org.junit.Assert.fail;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import org.junit.Test;

import com.google.appengine.api.rdbms.AppEngineDriver;
import com.google.code.gaeapptest.util.AppEngineUtil;

/*-
 * https://developers.google.com/cloud-sql/
 * 
 * For local testing
 * https://developers.google.com/cloud-sql/docs/developers_guide_java
 * --jvm_flag=-Drdbms.server=local \
 * --jvm_flag=-Drdbms.driver=com.mysql.jdbc.Driver \
 * --jvm_flag=-Drdbms.url=jdbc:mysql://localhost:3306/yourdatabase?user=username&password=password
 * 
 * To run these tests you must first create the database
 *    https://code.google.com/apis/console
 *    select Google Cloud SQL 
 *    select instance (or create a new one)
 *    CREATE DATABASE apptest;
 *    USE apptest; 
 *    CREATE TABLE data (prop1 VARCHAR(255),  entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));
 */
public class SqlTests extends BaseTest implements GaeTest {

	private static String TEST_TYPE = "Sql";

	@Test
	public void all() throws Exception {
		long start = System.currentTimeMillis();
		createEntity();
		readEntity();
		readUpdateEntity();
		updateEntity();
		deleteEntity();
		long elapsed = System.currentTimeMillis() - start;
		saveResult(TEST_TYPE, elapsed, "OK");
	}

	@Test
	public void createEntity() throws Exception {
		long elapsed = -1L;
		String msg = "UNKNOWN";
		Connection c = null;
		String DATA = "test data"+new Date();
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(AppEngineUtil.connectString);
			PreparedStatement stmt = c
					.prepareStatement("INSERT INTO data (prop1) VALUES( ? )");
			stmt.setString(1, DATA);
			long start = System.currentTimeMillis();
			int rows = stmt.executeUpdate();
			elapsed = System.currentTimeMillis() - start;
			if (rows != 1) {
				elapsed = -1;
				msg = "create failed";
			} else {
				msg = "OK";
			}
		} catch (Exception e) {
			msg = "Err:" + e.getMessage();
			fail("Err:" + e.getMessage());
			e.printStackTrace();
		} finally {
			try { // cleanup
				PreparedStatement stmt = c
						.prepareStatement("delete from data where prop1=? ");
				stmt.setString(1, DATA);
				stmt.executeUpdate();
			} catch (Exception e) {
			}
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
			saveResult(TEST_TYPE, elapsed, msg);
		}
	}

	@Test
	public void readEntity() throws Exception {
		long elapsed = -1L;
		String msg = "UNKNOWN";
		Connection c = null;
		String DATA = "test data"+new Date();
		ResultSet rs = null;
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(AppEngineUtil.connectString);
			PreparedStatement stmt = c
					.prepareStatement("INSERT INTO data (prop1) VALUES( ? )");
			stmt.setString(1, DATA);
			int rows = stmt.executeUpdate();
			stmt = c.prepareStatement("SELECT prop1 from data where prop1=?");
			stmt.setString(1, DATA);
			long start = System.currentTimeMillis();
			rs = stmt.executeQuery();
			elapsed = System.currentTimeMillis() - start;
			if (rows == 1 && rs.next()) {
				msg = "OK";
			} else {
				msg = "read failed";
			}
		} catch (Exception e) {
			msg = "Err:" + e.getMessage();
			fail("Err:" + e.getMessage());
			e.printStackTrace();
		} finally {
			try { // cleanup
				PreparedStatement stmt = c
						.prepareStatement("delete from data where prop1=?");
				stmt.setString(1, DATA);
				stmt.executeUpdate();
			} catch (Exception e) {
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ignore) {
				}
			}
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
			saveResult(TEST_TYPE, elapsed, msg);
		}
	}

	@Test
	public void updateEntity() throws Exception {
		long elapsed = -1L;
		String msg = "UNKNOWN";
		Connection c = null;
		String DATA = "test data"+new Date();
		String DATA1 = "test data1"+new Date();
		ResultSet rs = null;
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(AppEngineUtil.connectString);
			PreparedStatement stmt = c
					.prepareStatement("INSERT INTO data (prop1) VALUES( ? )");
			stmt.setString(1, DATA);
			int rows = stmt.executeUpdate();
			stmt = c.prepareStatement("UPDATE data set prop1 =?");
			stmt.setString(1, DATA1);
			long start = System.currentTimeMillis();
			rows = stmt.executeUpdate();
			elapsed = System.currentTimeMillis() - start;
			if (rows < 1) {
				msg = "OK";
			} else {
				msg = "update failed";
			}
		} catch (Exception e) {
			msg = "Err:" + e.getMessage();
			fail("Err:" + e.getMessage());
			e.printStackTrace();
		} finally {
			try { // cleanup
				PreparedStatement stmt = c
						.prepareStatement("delete from data where prop1=?");
				stmt.setString(1, DATA1);
				stmt.executeUpdate();
			} catch (Exception e) {
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ignore) {
				}
			}
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
			saveResult(TEST_TYPE, elapsed, msg);
		}
	}
	@Test
	public void readUpdateEntity() throws Exception {
		long elapsed = -1L;
		String msg = "UNKNOWN";
		Connection c = null;
		String DATA = "test data"+new Date();
		String DATA1 = "test data1"+new Date();
		ResultSet rs = null;
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(AppEngineUtil.connectString);
			PreparedStatement stmt = c
					.prepareStatement("INSERT INTO data (prop1) VALUES( ? )");
			stmt.setString(1, DATA);
			int rows = stmt.executeUpdate();
			stmt = c.prepareStatement("SELECT prop1 from data where prop1=?");
			stmt.setString(1, DATA);
			long start = System.currentTimeMillis();
			rs = stmt.executeQuery();
			stmt = c.prepareStatement("UPDATE data set prop1 =?");
			stmt.setString(1, DATA1);
			rows = stmt.executeUpdate();
			elapsed = System.currentTimeMillis() - start;
			if (rows < 1) {
				msg = "OK";
			} else {
				msg = "read update failed";
			}
		} catch (Exception e) {
			msg = "Err:" + e.getMessage();
			fail("Err:" + e.getMessage());
			e.printStackTrace();
		} finally {
			try { // cleanup
				PreparedStatement stmt = c
						.prepareStatement("delete from data where prop1=?");
				stmt.setString(1, DATA1);
				stmt.executeUpdate();
			} catch (Exception e) {
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ignore) {
				}
			}
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
			saveResult(TEST_TYPE, elapsed, msg);
		}
	}

	@Test
	public void deleteEntity() throws Exception {
		long elapsed = -1L;
		String msg = "UNKNOWN";
		Connection c = null;
		String DATA = "test data"+new Date();
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(AppEngineUtil.connectString);
			PreparedStatement stmt = c
					.prepareStatement("INSERT INTO data (prop1) VALUES( ? )");
			stmt.setString(1, DATA);
			int rows = stmt.executeUpdate();
			stmt = c.prepareStatement("delete from data where prop1=? ");
			stmt.setString(1, DATA);
			long start = System.currentTimeMillis();
			rows = stmt.executeUpdate();
			elapsed = System.currentTimeMillis() - start;
			if (rows != 1) {
				elapsed = -1;
				msg = "delete failed";
			} else {
				msg = "OK";
			}
		} catch (Exception e) {
			msg = "Err:" + e.getMessage();
			fail("Err:" + e.getMessage());
			e.printStackTrace();
		} finally {
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
			saveResult(TEST_TYPE, elapsed, msg);
		}
	}
}
